﻿using PSI.Helper;
using PSI.Models.TMS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace PSI.Services.TMS {
    public class PayableFeeService : PSIBaseService {
        public List<PayableFee> GetListData() {
            var result = new List<PayableFee>();

            var sql = "select id, f_name, pinyin from tms_bc_payable_fee order by f_name";
            Query(sql, reader => {
                result.Add(new PayableFee {
                    Id = reader.GetString(0),
                    Name = reader.GetString(1),
                    PinYin = reader.GetString(2)
                });
            });
            return result;
        }

        public Result Edit(PayableFee fee) {
            if (string.IsNullOrEmpty(fee.Id)) {
                // 新增

                var sql = "select count(*) from tms_bc_payable_fee where f_name = @name";
                var p = CreateDbParameters();
                p.AddWithValue("name", fee.Name);
                var cnt = QueryCount(sql, p);
                if (cnt > 0) {
                    return new Result { Success = false, Messgae = "应付费目[" + fee.Name + "]已经存在" };
                }

                fee.Id = IdGeneratorService.NewId();
                if (string.IsNullOrEmpty(fee.PinYin)) {
                    fee.PinYin = new PinYinHelper().ConvertToPinYinZiTou(fee.Name, true);
                } else {
                    fee.PinYin = fee.PinYin.ToUpper();
                }

                sql = @"insert into tms_bc_payable_fee(id, f_name, pinyin)
                        values (@id, @name, @pinyin)";
                p = CreateDbParameters();
                p.AddWithValue("id", fee.Id);
                p.AddWithValue("name", fee.Name);
                p.AddWithValue("pinyin", fee.PinYin);
                Execute(sql, p);
            } else {
                // 编辑
                var sql = "select count(*) from tms_bc_payable_fee where f_name = @name and id <> @id";
                var p = CreateDbParameters();
                p.AddWithValue("id", fee.Id);
                p.AddWithValue("name", fee.Name);
                var cnt = QueryCount(sql, p);
                if (cnt > 0) {
                    return new Result { Success = false, Messgae = "应付费目[" + fee.Name + "]已经存在" };
                }
                if (string.IsNullOrEmpty(fee.PinYin)) {
                    fee.PinYin = new PinYinHelper().ConvertToPinYinZiTou(fee.Name, true);
                } else {
                    fee.PinYin = fee.PinYin.ToUpper();
                }

                sql = @"update tms_bc_payable_fee
                            set f_name = @name, pinyin = @pinyin
                        where id = @id";
                p = CreateDbParameters();
                p.AddWithValue("id", fee.Id);
                p.AddWithValue("name", fee.Name);
                p.AddWithValue("pinyin", fee.PinYin);
                Execute(sql, p);
            }

            return new Result { Success = true, Id = fee.Id };
        }

        public Result Delete(string id) {
            var sql = "delete from tms_bc_payable_fee where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            Execute(sql, p);

            return new Result { Success = true };
        }

        public List<Vsl> QueryData(string queryKey) {
            var result = new List<Vsl>();

            var sql = @"select top 20 f_name from tms_bc_payable_fee 
                        where f_name like @qk or pinyin like @qk
                        order by 1";
            var p = CreateDbParameters();
            p.AddWithValue("qk", "%" + queryKey + "%");
            Query(sql, p, reader => {
                result.Add(new Vsl {
                    Name = reader.GetString(0)
                });
            });

            return result;
        }
    }
}